﻿using System;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

namespace Shuba.Data
{
    public class SPADAFile
    {
        public SPADAFile()
        { 
        }

        public List<Shuba.Model.SPADAFileInfo> GetList(string FileNameUpload)
        {
            List<Shuba.Model.SPADAFileInfo> _list = new List<Shuba.Model.SPADAFileInfo>();
            Database mydb = DBConnection.CreateDatabase();

            string mysql = @"SELECT DATE_FILE_UPLOAD,DATE_FILE_MODIFIED,FILE_SIZE_UPLOAD,PATH_FILE_UPLOAD,NAME_FOLDER_UPLOAD,
			                FILE_NAME_UPLOAD,ENTRY_BY,ENTRY_DATE,UPDATE_BY,UPDATE_DATE,
			                (CONVERT(VARCHAR,DATE_FILE_UPLOAD,120) + '-' + FILE_NAME_UPLOAD) ROWNUM,VERSION,CHECKSUM
                            FROM TBL_MS_SPADA_FILE_VERSION
                            WHERE UPPER(FILE_NAME_UPLOAD) LIKE '%" + FileNameUpload.ToUpper() + "%'";

            DbCommand mycomm = mydb.GetSqlStringCommand(mysql);
            using (IDataReader dr = mydb.ExecuteReader(mycomm))
            {
                while (dr.Read())
                {
                    _list.Add(new Shuba.Model.SPADAFileInfo(dr.GetDateTime(0), dr.GetDateTime(1), dr.GetDouble(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetString(6), dr.GetDateTime(7), dr.GetString(8), dr.GetDateTime(9), dr.GetString(10), dr.GetString(11), dr.GetInt64(12)));
                    
                }
                dr.Close();
            }
            return _list;
        }

        public Shuba.Model.SPADAFileInfo GetDateSystem()
        {
            Shuba.Model.SPADAFileInfo list = new Shuba.Model.SPADAFileInfo();
            Database mydb = DBConnection.CreateDatabase();
            DbCommand mycomm;

            string mysql = "SELECT GETDATE()";

            mycomm = mydb.GetSqlStringCommand(mysql);

            using (IDataReader mydr = mydb.ExecuteReader(mycomm))
            {
                while (mydr.Read())
                {
                    list = new Shuba.Model.SPADAFileInfo(mydr.GetDateTime(0));
                }
                mydr.Close();
            }
            return list;
        }

        public int Save(Shuba.Model.SPADAFileInfo bi)
        {
            int status = 0;
            try
            {
                Database mydb = DBConnection.CreateDatabase();

                DbCommand mycmd = mydb.GetSqlStringCommand(@"INSERT INTO TBL_MS_SPADA_FILE_VERSION_TMP(DATE_FILE_UPLOAD,DATE_FILE_MODIFIED,FILE_SIZE_UPLOAD,PATH_FILE_UPLOAD,NAME_FOLDER_UPLOAD,FILE_NAME_UPLOAD,ENTRY_BY,ENTRY_DATE,UPDATE_BY,UPDATE_DATE,ACT_TYPE) 
                                VALUES(@DATE_FILE_UPLOAD,@DATE_FILE_MODIFIED,@FILE_SIZE_UPLOAD,@PATH_FILE_UPLOAD,@NAME_FOLDER_UPLOAD,@FILE_NAME_UPLOAD,@ENTRY_BY,@ENTRY_DATE,@UPDATE_BY,@UPDATE_DATE,@ACT_TYPE)");

                mydb.AddInParameter(mycmd, "@DATE_FILE_UPLOAD", DbType.DateTime, bi.DATE_FILE_UPLOAD);
                mydb.AddInParameter(mycmd, "@DATE_FILE_MODIFIED", DbType.DateTime, bi.DATE_FILE_MODIFIED);
                mydb.AddInParameter(mycmd, "@FILE_SIZE_UPLOAD", DbType.Double, bi.FILE_SIZE_UPLOAD);
                mydb.AddInParameter(mycmd, "@PATH_FILE_UPLOAD", DbType.String, bi.PATH_FILE_UPLOAD);
                mydb.AddInParameter(mycmd, "@NAME_FOLDER_UPLOAD", DbType.String, bi.NAME_FOLDER_UPLOAD);
                mydb.AddInParameter(mycmd, "@FILE_NAME_UPLOAD", DbType.String, bi.FILE_NAME_UPLOAD);
                mydb.AddInParameter(mycmd, "@ENTRY_BY", DbType.String, bi.ENTRY_BY);
                mydb.AddInParameter(mycmd, "@ENTRY_DATE", DbType.DateTime, bi.ENTRY_DATE);
                mydb.AddInParameter(mycmd, "@UPDATE_BY", DbType.String, bi.UPDATE_BY);
                mydb.AddInParameter(mycmd, "@UPDATE_DATE", DbType.DateTime, bi.UPDATE_DATE);
                mydb.AddInParameter(mycmd, "@ACT_TYPE", DbType.String, bi.ACT_TYPE);

                status = mydb.ExecuteNonQuery(mycmd);
            }
            catch (Exception) { }
            return status;
        }

        public int Save2(Shuba.Model.SPADAFileInfo bi)
        {
            int status = 0;
            try
            {
                Database mydb = DBConnection.CreateDatabase();

                DbCommand mycmd = mydb.GetSqlStringCommand(@"INSERT INTO TBL_MS_SPADA_FILE_VERSION_TMP(DATE_FILE_UPLOAD,DATE_FILE_MODIFIED,FILE_SIZE_UPLOAD,PATH_FILE_UPLOAD,NAME_FOLDER_UPLOAD,FILE_NAME_UPLOAD,ENTRY_BY,ENTRY_DATE,UPDATE_BY,UPDATE_DATE,ACT_TYPE,VERSION,CHECKSUM) 
                                VALUES(@DATE_FILE_UPLOAD,@DATE_FILE_MODIFIED,@FILE_SIZE_UPLOAD,@PATH_FILE_UPLOAD,@NAME_FOLDER_UPLOAD,@FILE_NAME_UPLOAD,@ENTRY_BY,@ENTRY_DATE,@UPDATE_BY,@UPDATE_DATE,@ACT_TYPE,@VERSION,@CHECKSUM)");

                mydb.AddInParameter(mycmd, "@DATE_FILE_UPLOAD", DbType.DateTime, bi.DATE_FILE_UPLOAD);
                mydb.AddInParameter(mycmd, "@DATE_FILE_MODIFIED", DbType.DateTime, bi.DATE_FILE_MODIFIED);
                mydb.AddInParameter(mycmd, "@FILE_SIZE_UPLOAD", DbType.Double, bi.FILE_SIZE_UPLOAD);
                mydb.AddInParameter(mycmd, "@PATH_FILE_UPLOAD", DbType.String, bi.PATH_FILE_UPLOAD);
                mydb.AddInParameter(mycmd, "@NAME_FOLDER_UPLOAD", DbType.String, bi.NAME_FOLDER_UPLOAD);
                mydb.AddInParameter(mycmd, "@FILE_NAME_UPLOAD", DbType.String, bi.FILE_NAME_UPLOAD);
                mydb.AddInParameter(mycmd, "@ENTRY_BY", DbType.String, bi.ENTRY_BY);
                mydb.AddInParameter(mycmd, "@ENTRY_DATE", DbType.DateTime, bi.ENTRY_DATE);
                mydb.AddInParameter(mycmd, "@UPDATE_BY", DbType.String, bi.UPDATE_BY);
                mydb.AddInParameter(mycmd, "@UPDATE_DATE", DbType.DateTime, bi.UPDATE_DATE);
                mydb.AddInParameter(mycmd, "@ACT_TYPE", DbType.String, bi.ACT_TYPE);
                mydb.AddInParameter(mycmd, "@VERSION", DbType.String, bi.VERSION);
                mydb.AddInParameter(mycmd, "@CHECKSUM", DbType.Int64, bi.CHECKSUM);
                status = mydb.ExecuteNonQuery(mycmd);
            }
            catch (Exception e) { }
            return status;
        }

        public List<Shuba.Model.SPADAFileInfo> GetListApproval(string UpdateBy)
        {
            List<Shuba.Model.SPADAFileInfo> _SPADAFileInfo = new List<Shuba.Model.SPADAFileInfo>();

            Database mydb = DBConnection.CreateDatabase();

            string mysql = @"SELECT DATE_FILE_UPLOAD,DATE_FILE_MODIFIED,FILE_SIZE_UPLOAD,PATH_FILE_UPLOAD,NAME_FOLDER_UPLOAD,
                                FILE_NAME_UPLOAD,ENTRY_BY,ENTRY_DATE,UPDATE_BY,UPDATE_DATE,ACT_TYPE,
                                (CONVERT(VARCHAR,DATE_FILE_UPLOAD,120) + '-' + FILE_NAME_UPLOAD) ROWNUM,VERSION,CHECKSUM
                                FROM TBL_MS_SPADA_FILE_VERSION_TMP
                                WHERE (RTRIM(UPDATE_BY) <> '" + UpdateBy + "') ORDER BY DATE_FILE_UPLOAD";
            DbCommand mycomm = mydb.GetSqlStringCommand(mysql);

            using (IDataReader dr = mydb.ExecuteReader(mycomm))
            {
                while (dr.Read())
                {
                    _SPADAFileInfo.Add(new Shuba.Model.SPADAFileInfo(dr.GetDateTime(0), dr.GetDateTime(1), dr.GetDouble(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetString(6), dr.GetDateTime(7), dr.GetString(8), dr.GetDateTime(9), dr.GetString(10), dr.GetString(11), dr.GetString(12), dr.GetInt64(13)));
                            
                }
                dr.Close();
            }
            return _SPADAFileInfo;
        }

        public Shuba.Model.SPADAFileInfo GetApprovalByID(string id)
        {
            Shuba.Model.SPADAFileInfo _list = new Shuba.Model.SPADAFileInfo();
            Database mydb = DBConnection.CreateDatabase();

            string mysql = @"SELECT DATE_FILE_UPLOAD,DATE_FILE_MODIFIED,FILE_SIZE_UPLOAD,PATH_FILE_UPLOAD,NAME_FOLDER_UPLOAD,
                                FILE_NAME_UPLOAD,ENTRY_BY,ENTRY_DATE,UPDATE_BY,UPDATE_DATE,ACT_TYPE,
                                (CONVERT(VARCHAR,DATE_FILE_UPLOAD,120) + '-' + FILE_NAME_UPLOAD) ROWNUM,VERSION,CHECKSUM
                                FROM dbo.TBL_MS_SPADA_FILE_VERSION_TMP
                                WHERE (CONVERT(VARCHAR,DATE_FILE_UPLOAD,120) + '-' + FILE_NAME_UPLOAD) = '" + id + "' ";
            DbCommand mycomm = mydb.GetSqlStringCommand(mysql);

            using (IDataReader dr = mydb.ExecuteReader(mycomm))
            {
                while (dr.Read())
                {
                    _list = new Shuba.Model.SPADAFileInfo(dr.GetDateTime(0), dr.GetDateTime(1), dr.GetDouble(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetString(6), dr.GetDateTime(7), dr.GetString(8), dr.GetDateTime(9), dr.GetString(10), dr.GetString(11), dr.GetString(12), dr.GetInt64(13));
                }
                dr.Close();
            }
            return _list;
        }

        public void Approve(string ActType, string id,string FileNameUpload,DateTime DateFileUpload)
        {
            Database mydb = DBConnection.CreateDatabase();
            DbCommand mycomm = mydb.GetStoredProcCommand("PROC_APPROVAL_SPADA_FILE_VERSION");
            mydb.AddInParameter(mycomm, "@ACT_TYPE", DbType.String, ActType);
            mydb.AddInParameter(mycomm, "@ID", DbType.String, id);
            mydb.AddInParameter(mycomm, "@FILE_NAME_UPLOAD", DbType.String, FileNameUpload);
            mydb.AddInParameter(mycomm, "@DATE_FILE_UPLOAD", DbType.DateTime, DateFileUpload);

            mydb.ExecuteNonQuery(mycomm);
        }

        public void Reject(string id)
        {
            Database mydb = DBConnection.CreateDatabase();

            string mysql = @"DELETE FROM TBL_MS_SPADA_FILE_VERSION_TMP
            			        WHERE CONVERT(VARCHAR,DATE_FILE_UPLOAD,120) + '-' + FILE_NAME_UPLOAD = '" + id + "' ";
            DbCommand mycmd = mydb.GetSqlStringCommand(mysql);

            mydb.ExecuteNonQuery(mycmd);
        }

        public Shuba.Model.SPADAFileInfo GetInfo(string id)
        {
            Shuba.Model.SPADAFileInfo _list = new Shuba.Model.SPADAFileInfo();
            Database mydb = DBConnection.CreateDatabase();

            string mysql = @"SELECT DATE_FILE_UPLOAD,DATE_FILE_MODIFIED,FILE_SIZE_UPLOAD,PATH_FILE_UPLOAD,NAME_FOLDER_UPLOAD,
            			        FILE_NAME_UPLOAD,ENTRY_BY,ENTRY_DATE,UPDATE_BY,UPDATE_DATE,
            			        (CONVERT(VARCHAR,DATE_FILE_UPLOAD,120) + '-' + FILE_NAME_UPLOAD)ROWNUM,VERSION,CHECKSUM
            			        FROM TBL_MS_SPADA_FILE_VERSION
            			        WHERE CONVERT(VARCHAR,DATE_FILE_UPLOAD,120) + '-' + FILE_NAME_UPLOAD = '" + id + "' ";
            DbCommand mycomm = mydb.GetSqlStringCommand(mysql);

            using (IDataReader dr = mydb.ExecuteReader(mycomm))
            {
                while (dr.Read())
                {
                    _list = new Shuba.Model.SPADAFileInfo(dr.GetDateTime(0), dr.GetDateTime(1), dr.GetDouble(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetString(6), dr.GetDateTime(7), dr.GetString(8), dr.GetDateTime(9), dr.GetString(10), dr.GetString(11), dr.GetInt64(12));
                }
                dr.Close();
            }
            return _list;
        }

        //cek apakah file yang didelete file yang paling update ? kalo ya tidak boleh didelete
        public bool CheckIsUsedFileUpdate(DateTime dateFileUpload)
        {
            bool status = false;
            try
            {
                Database mydb = DBConnection.CreateDatabase();

                string mysql = @"SELECT [DATE_FILE_UPLOAD],[DATE_FILE_MODIFIED]
                                      ,[FILE_SIZE_UPLOAD],[PATH_FILE_UPLOAD]
                                      ,[NAME_FOLDER_UPLOAD],[FILE_NAME_UPLOAD]
                                      ,[ENTRY_BY],[ENTRY_DATE]
                                      ,[UPDATE_BY],[UPDATE_DATE]
                                FROM TBL_MS_SPADA_FILE_VERSION
                                WHERE [UPDATE_DATE] = (SELECT MAX([UPDATE_DATE]) 
                                                        FROM TBL_MS_SPADA_FILE_VERSION AS F 
                                                        WHERE SUBSTRING(F.[FILE_NAME_UPLOAD], 1, CHARINDEX('~', F.[FILE_NAME_UPLOAD]) - 1) = SUBSTRING(TBL_MS_SPADA_FILE_VERSION.[FILE_NAME_UPLOAD], 1, CHARINDEX('~', TBL_MS_SPADA_FILE_VERSION.[FILE_NAME_UPLOAD]) - 1))
                                AND (DATEPART(year, DATE_FILE_UPLOAD) = @YEAR AND
                                    DATEPART(month, DATE_FILE_UPLOAD) = @MONTH AND 
                                    DATEPART(day, DATE_FILE_UPLOAD) = @DAY AND
                                    DATEPART(hour, DATE_FILE_UPLOAD) = @HOUR AND
                                    DATEPART(minute, DATE_FILE_UPLOAD) = @MINUTE AND
                                    DATEPART(second, DATE_FILE_UPLOAD) = @SECOND AND
                                    DATEPART(millisecond, DATE_FILE_UPLOAD) = @MILLISECOND)";
                DbCommand mycomm = mydb.GetSqlStringCommand(mysql);
                mydb.AddInParameter(mycomm, "@YEAR", DbType.Int32, dateFileUpload.Year);
                mydb.AddInParameter(mycomm, "@MONTH", DbType.Int32, dateFileUpload.Month);
                mydb.AddInParameter(mycomm, "@DAY", DbType.Int32, dateFileUpload.Day);
                mydb.AddInParameter(mycomm, "@HOUR", DbType.Int32, dateFileUpload.Hour);
                mydb.AddInParameter(mycomm, "@MINUTE", DbType.Int32, dateFileUpload.Minute);
                mydb.AddInParameter(mycomm, "@SECOND", DbType.Int32, dateFileUpload.Second);
                mydb.AddInParameter(mycomm, "@MILLISECOND", DbType.Int32, dateFileUpload.Millisecond);

                using (IDataReader mydr = mydb.ExecuteReader(mycomm))
                {
                    while (mydr.Read())
                    {
                        status = true;
                    }
                    mydr.Close();
                }
            }
            catch (Exception) { }
            return status;
        }

        public string CalculateCheksum(string file)
        {
            Shuba.Data.AdlerCheckSum acs = new Shuba.Data.AdlerCheckSum();
            if (acs.MakeForFile(file))
            {   //success
                return acs.ToString();
            }
            else
            {   //failure
                return "Unable to get checksum!";
            }
        }

        public int getVersion(string version) 
        {
            int jmlRow = 0;
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"SELECT count(*) AS jmlData FROM(
                    SELECT * FROM TBL_MS_SPADA_FILE_VERSION WHERE VERSION = @PARAM) AS DATA";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, version);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                    jmlRow = (int)dr["jmlData"];
                dr.Close();
            }
            return jmlRow;
        }
    }
}